package com.qulai.amazon_us.mapper.basic;

import com.qulai.amazon_us.domain.basic.dashboard.ProductChangeDTO;
import com.qulai.amazon_us.domain.basic.dashboard.RemainUploadDTO;
import com.qulai.amazon_us.domain.basic.dashboard.ShopStockStatsDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

public interface DashboardMapper {

    /**
     * 数据看板，获取产品数据变化更新的
     *
     * @param startTime
     * @param endTime
     * @param className
     * @param platform
     * @return
     */
    @Select({
            "<script>",
            "SELECT",
            "  create_time AS createTime,",
            "  class_name AS className,",
            "  platform",
            "FROM basic_product",
            "WHERE",
            "  create_time BETWEEN #{startTime} AND #{endTime}",
            "  <if test='className != null'>AND class_name = #{className}</if>",
            "  <if test='platform != null'>AND platform = #{platform}</if>",
            "</script>"
    })
    List<ProductChangeDTO> getProductAddChange(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("className") String className,
            @Param("platform") String platform
    );


    @Select("SELECT class_name AS className, COUNT(basic_id) AS total " +
            "FROM basic_image " +
            "WHERE published = 1 and status = 1 and  platform = #{platform} and class_name !=''" +
            "GROUP BY class_name")
    List<RemainUploadDTO> getProductTotalByClassName(@Param("platform") String platform);


    @Select(" SELECT class_name AS className, COUNT(basic_id) AS used FROM amazon_us_record WHERE brand = #{brand} AND basic_platform = #{platform} GROUP BY class_name")
    List<RemainUploadDTO> getProductUsedByClass(@Param("platform") String platform, @Param("brand") String brand);


    @Select("SELECT shop, " +
            "COUNT(*) AS total, " +
            "SUM(CASE WHEN quantity <= 4 THEN 1 ELSE 0 END) AS lowStock " +
            "FROM amazon_us_record " +
            "GROUP BY shop")
    List<ShopStockStatsDTO> selectShopStockStats();

}
